package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pos.domain.SubType;
import pos.domain.SubTypeImpl;
import util.UIDGenerator;

public class JDBCSubTypeDAO implements ISubTypeDAO{

	/**
	 * Attribute which create the connection to the DB
	 */
	private Connection conn;

	/**
	 * Constructor
	 */
	public JDBCSubTypeDAO() {
		conn = (Connection) ConnectionManager.getInstance().checkOut();
	}

	protected void finalize() {
		ConnectionManager.getInstance().checkIn(conn);
	}
	
	public List<SubType> getAllSubTypes() {

		String sql = "SELECT * FROM subtype";
		PreparedStatement stmt = null;
		ResultSet result = null;
		List<SubType> listSubTypes = null;

		try {
			stmt = conn.prepareStatement(sql);

			result = stmt.executeQuery();
			listSubTypes = new ArrayList<SubType>();

			while (result.next()) {
				SubType subType = new SubTypeImpl();
				subType.setId(result.getString("idsubtype"));
				subType.setName(result.getString("name"));

				listSubTypes.add(subType);

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {
			try {
				if (result != null) {
					conn.close();
				}
				if (stmt != null) {
					conn.close();
				}
			} catch (SQLException e) {
			}
		}

		return listSubTypes;
	}

	public SubType getSubTypeById(String idsubtype) {

		SubType subType = new SubTypeImpl();
		ResultSet result = null;
		PreparedStatement stmt = null;

		// SQL query for select the SubType with that ID.
		String sql = "SELECT * FROM subtype WHERE (idsubtype = ?)";

		try {
			// Query execution
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, idsubtype);
			result = stmt.executeQuery();

			// Treatment of query
			while (result.next()){
				subType.setId(result.getString("idsubtype"));
				subType.setName(result.getString("name"));

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}

			} catch (Exception e) {

			}
		}

		return subType;
	}
	
	
	public SubType getSubTypeByName(String name) {

		SubType subType = new SubTypeImpl();
		ResultSet result = null;
		PreparedStatement stmt = null;

		// SQL query for select the SubType with that ID.
		String sql = "SELECT * FROM subtype WHERE (name = ?)";

		try {
			// Query execution
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, name);
			result = stmt.executeQuery();

			// Treatment of query
			while (result.next()){
				subType.setId(result.getString("idsubtype"));
				subType.setName(result.getString("name"));

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}

			} catch (Exception e) {

			}
		}

		return subType;
	}

	public List<SubType> getListSubTypesByEntityAndType(String idEntity, String idType) {

		List<SubType> listSubTypes = new ArrayList<SubType>();
		
		PreparedStatement stm = null;
		ResultSet result = null;
		String sql = "SELECT * FROM subtype WHERE (identity = ?) AND (idtype = ?)";
		
		try {
			stm = conn.prepareStatement(sql);
			stm.setString(1, idEntity);
			stm.setString(2, idType);
			result = stm.executeQuery();
			while (result.next()) {
				SubType subType = new SubTypeImpl();
				subType.setId(result.getString("idsubtype"));
				subType.setName(result.getString("name"));
				listSubTypes.add(subType);
			}
		} catch (SQLException e) {
			System.out.println("SQLMessage: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stm != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}
			} catch (SQLException e) {

			}
		}
		return listSubTypes;
	}

	public List<SubType> getListSubTypesByEntity(String idEntity) {

		List<SubType> listSubTypes = new ArrayList<SubType>();
		PreparedStatement stm = null;
		ResultSet result = null;
		String sql = "SELECT * FROM subtype WHERE (identity = ?)";
		
		try {
			stm = conn.prepareStatement(sql);
			stm.setString(1, idEntity);
			result = stm.executeQuery();
			while (result.next()) {
				SubType subType = new SubTypeImpl();
				subType.setId(result.getString("idsubtype"));
				subType.setName(result.getString("name"));
				listSubTypes.add(subType);
			}
		} catch (SQLException e) {
			System.out.println("SQLMessage: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stm != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}
			} catch (SQLException e) {

			}
		}
		return listSubTypes;
	}
	public SubType insertSubType(SubType subType, String idType, String idEntity) {

		PreparedStatement stmt = null;
		String idsubtype = "";
		if(subType.getId()==null){
			idsubtype = UIDGenerator.getInstance().getKey(); // ID for DB
			subType.setId(idsubtype); // ID for object
		}
		idsubtype = subType.getId();

		String sql = "INSERT INTO subtype (idsubtype,name,idtype,identity) VALUES (?,?,?,?)";

		try {
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, idsubtype);
			stmt.setString(2, subType.getName());
			stmt.setString(3, idType);
			stmt.setString(4, idEntity);

			stmt.executeUpdate();
			
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("SQL State: " + e.getSQLState());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();

		} finally {
			try {
				if (stmt != null) {
					conn.close();
				}
			} catch (SQLException e) {

			}
		}
		return subType;
		
	}

	public void removeSubType(SubType subType) {

		PreparedStatement stmt = null;
		
		// Also remove the associations with Type and Entity? -> mmm...We want a lot of data.

		String sql = "DELETE FROM subtype WHERE (idsubtype = ?)";

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, subType.getId());

			stmt.executeUpdate();

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {

			try {
				if (stmt != null) {
					conn.close();
				}
			} catch (Exception e) {

			}
		}
		
	}

	public boolean existSubType(String name, String idType, String idEntity) {

		PreparedStatement stmt = null;
		ResultSet result = null;

		String sql = "SELECT * FROM subtype WHERE (name = ?) AND (idtype = ?) AND (identity = ?)";
		boolean res = false;

		try {

			stmt = conn.prepareStatement(sql);
			stmt.setString(1, name);
			stmt.setString(2, idType);
			stmt.setString(3, idEntity);
			result = stmt.executeQuery();

			if (result.next()==false) {
				res = false;
			} else {
				res = true;
		}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("Error Code: " + e.getErrorCode());
			System.out.println("Cause: " + e.getCause());
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null) {
					conn.close();
				}
				if (result != null) {
					conn.close();
				}
			} catch (Exception e) {
			}
		}
		return res;
	}

}